/*
 * This file is released under the terms of the Artistic License.  Please see
 * the file LICENSE, included in this package, for details.
 *
 * Copyright (C) 2006 Rilson Nascimento
 *
 * Trade Lookup transaction
 * ------------------------
 * The Trade-Lookup transaction is designed to emulate the information
 * retrieval by 
 * either a customer or a broker to satisfy their questions regarding a
 * particular account, a group of trade transaction identifiers or a 
 * particular security
 *
 * Based on TPC-E Standard Specification Draft Revision 0.32.2e Clause 3.3.3.
 */

/*
 * Frame 1
 * responsible for retrieving information about the specified array of trade
 * IDs.
 */

CREATE OR REPLACE FUNCTION TradeLookupFrame1 (
                        IN max_trades INTEGER,
                        IN trade_id BIGINT[],
                        OUT bid_price NUMERIC(8,2)[],
                        OUT cash_transaction_amount NUMERIC(10,2)[],
                        OUT cash_transaction_dts TIMESTAMP[],
                        OUT cash_transaction_name VARCHAR(100)[],
                        OUT exec_name VARCHAR(64)[],
                        OUT is_cash BOOLEAN[],
                        OUT is_market BOOLEAN[],
                        OUT num_found INTEGER,
                        OUT settlement_amount NUMERIC(10,2)[],
                        OUT settlement_cash_due_date TIMESTAMP[],
                        OUT settlement_cash_type VARCHAR(40)[],
                        OUT trade_history_dts VARCHAR,
                        OUT trade_history_status_id VARCHAR,
                        OUT trade_price NUMERIC(8,2)[]
) RETURNS SETOF record AS $$
DECLARE
    _trade_history_dts           TIMESTAMP[];
    _trade_history_status_id     VARCHAR(4)[];
    _char_th_dts                 VARCHAR;
    _char_thst_id                VARCHAR;
    _trade_id                    BIGINT;
	-- variables
	i		INTEGER;
    _row_count INTEGER;
	rs		RECORD;
BEGIN
	num_found = 0;
    trade_history_dts = '{';
    trade_history_status_id = '{';

	i = 1;
	WHILE i <= max_trades LOOP
		-- Get trade information
		-- Should only return one row for each trade
        _trade_id = trade_id[i];
        _row_count = 0;
        FOR rs IN
   		    SELECT
                T_BID_PRICE,
	            T_EXEC_NAME,
		        T_IS_CASH,
		        TT_IS_MRKT,
		        T_TRADE_PRICE
            FROM
                TRADE,
			    TRADE_TYPE
            WHERE	
                T_ID = _trade_id AND
			    T_TT_ID = TT_ID
        LOOP
            _row_count = _row_count + 1;
            bid_price[i] = rs.T_BID_PRICE;
            exec_name[i] = rs.T_EXEC_NAME;
            is_cash[i] = rs.T_IS_CASH;
            is_market[i] = rs.TT_IS_MRKT;
            trade_price[i] = rs.T_TRADE_PRICE;
        END LOOP;

        num_found = num_found + _row_count;
        
		-- Get settlement information
		-- Should only return one row for each trade
        
		FOR rs IN 
            SELECT	
                SE_AMT,
			    SE_CASH_DUE_DATE,
			    SE_CASH_TYPE
		    FROM	
                SETTLEMENT
		    WHERE	
                SE_T_ID = _trade_id
        LOOP
            settlement_amount[i] = rs.SE_AMT;
            settlement_cash_due_date[i] = rs.SE_CASH_DUE_DATE;
            settlement_cash_type[i] = rs.SE_CASH_TYPE;
        END LOOP;
		-- get cash information if this is a cash transaction
		-- Should only return one row for each trade that was a cash transaction
		IF is_cash[i] THEN
			FOR rs IN
                SELECT
                    CT_AMT,
				    CT_DTS,
				    CT_NAME
			    FROM	
                    CASH_TRANSACTION
		        WHERE	
                    CT_T_ID = _trade_id
            LOOP
                cash_transaction_amount[i] = rs.CT_AMT;
                cash_transaction_dts[i] = rs.CT_DTS;
                cash_transaction_name[i] = rs.CT_NAME;
            END LOOP;
        ELSE
            cash_transaction_amount[i] = NULL;
            cash_transaction_dts[i] = NULL;
            cash_transaction_name[i] = NULL;
		END IF;

		-- read trade_history for the trades
		-- Should return 2 to 3 rows per trade
        _trade_history_dts = '{}';
        _trade_history_status_id = '{}';
		FOR rs IN 
            SELECT 
                   TH_DTS, 
                   TH_ST_ID 
			FROM 
                   TRADE_HISTORY
			WHERE 
                   TH_T_ID = _trade_id 
            ORDER BY 
                   TH_DTS 
        LOOP     
            _trade_history_dts = _trade_history_dts || rs.TH_DTS;
			_trade_history_status_id = _trade_history_status_id || rs.TH_ST_ID;
		END LOOP;

        _char_th_dts = _trade_history_dts;
        _char_thst_id = _trade_history_status_id;

        trade_history_dts = trade_history_dts || _char_th_dts || ',';
        trade_history_status_id = trade_history_status_id || _char_thst_id || ',';

		i = i + 1;
	END LOOP;

    trade_history_dts = trim(trailing from trade_history_dts, ',');
    trade_history_dts = trade_history_dts || '}';

    trade_history_status_id = trim(trailing from trade_history_status_id, ',');
    trade_history_status_id = trade_history_status_id || '}';

    RETURN NEXT;
END;
$$ LANGUAGE 'plpgsql';


/*
 * Frame 2
 * returns information for the first N (max_trades) trades executed for the
 * specified customer account at or after the specified time.
 */

CREATE OR REPLACE FUNCTION TradeLookupFrame2(
                        IN acct_id BIGINT,
                        IN end_trade_dts TIMESTAMP,
                        IN max_trades INTEGER,
                        IN start_trade_dts TIMESTAMP,
                        OUT bid_price NUMERIC(8,2)[],
                        OUT cash_transaction_amount NUMERIC(10,2)[],
                        OUT cash_transaction_dts TIMESTAMP[],
                        OUT cash_transaction_name VARCHAR(100)[],
                        OUT exec_name VARCHAR(64)[],
                        OUT is_cash BOOLEAN[],
                        OUT num_found INTEGER,
                        OUT settlement_amount NUMERIC(10,2)[],
                        OUT settlement_cash_due_date TIMESTAMP[],
                        OUT settlement_cash_type VARCHAR(40)[],
                        OUT trade_history_dts VARCHAR,
                        OUT trade_history_status_id VARCHAR,
                        OUT trade_list BIGINT[],
                        OUT trade_price NUMERIC(8,2)[]
) RETURNS SETOF record AS $$
DECLARE
	-- output parameters
	_trade_history_dts		        TIMESTAMP[];
	_trade_history_status_id		VARCHAR(4)[];
    _char_th_dts                    VARCHAR;
    _char_thst_id                   VARCHAR;
    _trade_id                       BIGINT;
	i		INTEGER;
	rs		RECORD;
BEGIN
	-- Get trade information
	-- Should return between 0 and max_trades rows
    trade_history_dts = '{';
    trade_history_status_id = '{';
	i = 0;
	FOR rs IN 
        SELECT
            T_BID_PRICE,
			T_EXEC_NAME,
			T_IS_CASH,
			T_ID,
			T_TRADE_PRICE
		FROM
            TRADE
		WHERE
            T_CA_ID = acct_id AND
			T_DTS >= start_trade_dts AND
            T_DTS <= end_trade_dts
		ORDER BY T_DTS asc
		LIMIT max_trades
    LOOP
        i = i + 1;
        bid_price[i] = rs.T_BID_PRICE;
        exec_name[i] = rs.T_EXEC_NAME;
        is_cash[i] = rs.T_IS_CASH;
        trade_list[i] = rs.T_ID;
        trade_price[i] = rs.T_TRADE_PRICE;
    END LOOP;

    num_found = i;

    i = 1;
    WHILE i <= num_found LOOP

		-- Get settlement information
		-- Should return only one row for each trade
        _trade_id = trade_list[i];
		FOR rs IN
           SELECT	
                SE_AMT,
			    SE_CASH_DUE_DATE,
			    SE_CASH_TYPE
		    FROM 
                SETTLEMENT
		    WHERE
                SE_T_ID = _trade_id
        LOOP
            settlement_amount[i] = rs.SE_AMT;
            settlement_cash_due_date[i] = rs.SE_CASH_DUE_DATE;
            settlement_cash_type[i] = rs.SE_CASH_TYPE;
        END LOOP;

		-- get cash information if this is a cash transaction
		-- Should return only one row for each trade that was a cash transaction

		IF is_cash[i] THEN
			FOR rs IN
                SELECT
                    CT_AMT,
 				    CT_DTS,
				    CT_NAME
			    FROM	
                    CASH_TRANSACTION
			    WHERE 
                    CT_T_ID = _trade_id
            LOOP
                cash_transaction_amount[i] = rs.CT_AMT;
                cash_transaction_dts[i] = rs.CT_DTS;
                cash_transaction_name[i] = rs.CT_NAME;
            END LOOP;
        ELSE
            cash_transaction_amount[i] = NULL;
            cash_transaction_dts[i] = NULL;
            cash_transaction_name[i] = NULL; 
		END IF;

		-- read trade_history for the trades
		-- Should return 2 to 3 rows per trade
        _trade_history_dts = '{}';
        _trade_history_status_id = '{}';
		FOR rs IN
            SELECT 
                 TH_DTS, TH_ST_ID 
			FROM 
                 TRADE_HISTORY
			WHERE 
                 TH_T_ID = _trade_id
            ORDER BY TH_DTS 
        LOOP
			_trade_history_dts = _trade_history_dts || rs.TH_DTS;
			_trade_history_status_id = _trade_history_status_id || rs.TH_ST_ID;
		END LOOP;

        _char_th_dts = _trade_history_dts;
        _char_thst_id = _trade_history_status_id;

        trade_history_dts = trade_history_dts || _char_th_dts || ',';
        trade_history_status_id = trade_history_status_id || _char_thst_id || ',';

        i = i + 1;
    END LOOP;

    trade_history_dts = trim(trailing from trade_history_dts, ',');
    trade_history_dts = trade_history_dts || '}';

    trade_history_status_id = trim(trailing from trade_history_status_id, ',');
    trade_history_status_id = trade_history_status_id || '}';

    RETURN NEXT;
END;
$$ LANGUAGE 'plpgsql';


/*
 * Frame 3
 * returns up to N (max_trades) trades for a given security on or after a
 * specified point in time.
 */

CREATE OR REPLACE FUNCTION TradeLookupFrame3(
                        IN end_trade_dts TIMESTAMP,
                        IN max_acct_id BIGINT,
                        IN max_trades INTEGER,
                        IN start_trade_dts TIMESTAMP,
                        IN symbol char(15),
                       OUT acct_id BIGINT[],
                       OUT cash_transaction_amount NUMERIC(10,2)[],
                       OUT cash_transaction_dts TIMESTAMP[],
                       OUT cash_transaction_name VARCHAR(100)[],
                       OUT exec_name VARCHAR(64)[],
                       OUT is_cash BOOLEAN[],
                       OUT num_found INTEGER,
                       OUT price NUMERIC(8,2)[],
                       OUT quantity INTEGER[],
                       OUT settlement_amount NUMERIC(10,2)[],
                       OUT settlement_cash_due_date TIMESTAMP[],
                       OUT settlement_cash_type VARCHAR(40)[],
                       OUT trade_dts TIMESTAMP[],
                       OUT trade_history_dts VARCHAR,
                       OUT trade_history_status_id VARCHAR,
                       OUT trade_list BIGINT[],
                       OUT trade_type VARCHAR(3)[]
) RETURNS SETOF record AS $$
DECLARE
	-- Local Frame variables
    _trade_history_dts TIMESTAMP[];
    _trade_history_status_id VARCHAR(4)[];
    _char_th_dts VARCHAR;
    _char_thst_id VARCHAR;
    _trade_id                       BIGINT;
	i	INTEGER;
	rs	RECORD;
BEGIN
	-- Should return between 0 and max_trades rows.
    trade_history_dts = '{';
    trade_history_status_id = '{';

	i = 0;
	FOR rs IN 
        SELECT 
                T_CA_ID,
		   	    T_EXEC_NAME,
			    T_IS_CASH,
			    T_TRADE_PRICE,
			    T_QTY,
			    T_DTS,
                T_ID,
			    T_TT_ID
		FROM	
                TRADE
		WHERE	
                T_S_SYMB = symbol AND
			    T_DTS >= start_trade_dts AND
                T_DTS <= end_trade_dts
		ORDER BY 
                T_DTS asc
		LIMIT max_trades 
    LOOP
        i = i + 1;
        acct_id[i] = rs.T_CA_ID;
        exec_name[i] = rs.T_EXEC_NAME;
        is_cash[i] = rs.T_IS_CASH;
        price[i] = rs.T_TRADE_PRICE;
        quantity[i] = rs.T_QTY;
        trade_dts[i] = rs.T_DTS;
        trade_list[i] = rs.T_ID;
        trade_type[i] = rs.T_TT_ID;
    END LOOP;

    num_found = i;

    i = 1;
    WHILE i <= num_found LOOP
        -- Get extra information for each trade in the trade list.
        -- Get settlement information
        -- Should return only one row for each trade
        _trade_id = trade_list[i];
		FOR rs IN
            SELECT	
                SE_AMT,
			    SE_CASH_DUE_DATE,
			    SE_CASH_TYPE
		    FROM	
                SETTLEMENT
		    WHERE	
                SE_T_ID = _trade_id
        LOOP
            settlement_amount[i] = rs.SE_AMT;
			settlement_cash_due_date[i] = rs.SE_CASH_DUE_DATE;
			settlement_cash_type[i] = rs.SE_CASH_TYPE;
        END LOOP;
		-- get cash information if this is a cash transaction
		-- Should return only one row for each trade that was a cash transaction

		IF is_cash[i] THEN
			FOR rs IN
                SELECT
                   CT_AMT,
 				   CT_DTS,
				   CT_NAME
			    FROM	
                    CASH_TRANSACTION
			    WHERE	
                    CT_T_ID = _trade_id
            LOOP
                cash_transaction_amount[i] = rs.CT_AMT;
				cash_transaction_dts[i] = rs.CT_DTS;
				cash_transaction_name[i] = rs.CT_NAME;
            END LOOP;
        ELSE
            cash_transaction_amount[i] = NULL;
            cash_transaction_dts[i] = NULL;
            cash_transaction_name[i] = NULL; 
		END IF;

		-- read trade_history for the trades
		-- Should return 2 to 3 rows per trade
        _trade_history_dts = '{}';
        _trade_history_status_id = '{}';
		FOR rs IN
            SELECT 
                 TH_DTS, 
                 TH_ST_ID 
			FROM 
                 TRADE_HISTORY
			WHERE 
                 TH_T_ID = _trade_id
            ORDER BY 
                 TH_DTS asc
        LOOP
			_trade_history_dts = _trade_history_dts || rs.TH_DTS;
			_trade_history_status_id = _trade_history_status_id || rs.TH_ST_ID;
		END LOOP;
        
        _char_th_dts = _trade_history_dts;
        _char_thst_id = _trade_history_status_id;

        trade_history_dts = trade_history_dts || _char_th_dts || ',';
        trade_history_status_id = trade_history_status_id || _char_thst_id || ',';

        i = i + 1;
    END LOOP;

    trade_history_dts = trim(trailing from trade_history_dts, ',');
    trade_history_dts = trade_history_dts || '}';

    trade_history_status_id = trim(trailing from trade_history_status_id, ',');
    trade_history_status_id = trade_history_status_id || '}';

    RETURN NEXT;
END;
$$ LANGUAGE 'plpgsql';


/*
 * Frame 4
 * identifies the first trade for the specified customer account on or after
 * the specified time.
 */

CREATE OR REPLACE FUNCTION TradeLookupFrame4(
        IN acct_id BIGINT,
        IN start_trade_dts TIMESTAMP,
        OUT holding_history_id BIGINT[],
        OUT holding_history_trade_id BIGINT[],
        OUT num_found INTEGER,
        OUT num_trades_found INTEGER,
        OUT quantity_after INTEGER[],
        OUT quantity_before INTEGER[],
        OUT trade_id BIGINT
) RETURNS SETOF record AS $$
DECLARE
	-- Local Frame variables
	rs		RECORD;
    _row_count  INTEGER;
    i       INTEGER;
BEGIN
	SELECT
         T_ID
	INTO	
         trade_id
	FROM	
         TRADE
	WHERE	
         T_CA_ID = acct_id AND
		 T_DTS >= start_trade_dts
	ORDER BY 
         T_DTS asc
	LIMIT 1;

    GET DIAGNOSTICS _row_count = ROW_COUNT;
    num_trades_found = _row_count;

	-- The trade_id is used in the subquery to find the original trade_id
	-- (HH_H_T_ID), which then is used to list all the entries.
	-- Should return 0 to 20 rows.
	
    i = 0;
	FOR rs IN 
        SELECT 
            HH_H_T_ID,
			HH_T_ID,
			HH_BEFORE_QTY,
			HH_AFTER_QTY
	    FROM
            HOLDING_HISTORY
		WHERE
            HH_H_T_ID in
				(SELECT	
                        HH_H_T_ID
				 FROM	
                        HOLDING_HISTORY
				 WHERE	
                        HH_T_ID = trade_id)
	    LIMIT 20
	LOOP
        i = i + 1;
		holding_history_id[i] = rs.HH_H_T_ID;
        holding_history_trade_id[i] = rs.HH_T_ID;
        quantity_before[i] = rs.HH_BEFORE_QTY;
        quantity_after[i] = rs.HH_AFTER_QTY;
	END LOOP;

    num_found = i;

    RETURN NEXT;
END;
$$ LANGUAGE 'plpgsql';

